using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using DLL.Essences;

namespace Dll.DAL
{
    public class CityDAL
    {
        public static DataTable GetAllCity()
        {
            DataTable dt= new DataTable();
            OleDbConnection connection = new OleDbConnection();
            OleDbConnection connect = BasePath.connect;
            OleDbCommand command = new OleDbCommand();
            command.Connection = connect;
            command.CommandText = "select city.id, city.name, users.name,city.[date] from users,city where users.id=city.userID";
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);
            adapter.Fill(dt);
            return dt;
        }

        public static List<City> GetAllCity(string datasource,string tablename)
        {
            List<City> list= new List<City>();
            DataTable dt = new DataTable();
            OleDbConnection connection = new OleDbConnection();
            OleDbConnection connect = new OleDbConnection(Dll.BasePath.getConnectionFromDataSource(datasource));
            OleDbCommand command = new OleDbCommand();
            command.Connection = connect;
            command.CommandText = "select * from "+tablename;
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);
            adapter.Fill(dt);
            DataTableReader dtr = dt.CreateDataReader();
            City c;
            while (dtr.Read())
            {
                c = new City();
                c.Id = Convert.ToInt32(dtr.GetValue(0));
                c.Name = dtr.GetValue(1).ToString();
                c.UserID=1;
                c.Date = Convert.ToDateTime(dtr.GetValue(3));
                list.Add(c);
            }            
            return list;
        }

        public static DataTable GetCityByName(string name)
        {
            name += "%";
            DataTable dt = new DataTable();
            OleDbConnection connection = new OleDbConnection();
            OleDbConnection connect = BasePath.connect;
            OleDbCommand command = new OleDbCommand();
            command.Connection = connect;
            command.CommandText = "select * from city where name like @name";
            command.Parameters.AddWithValue("@name", name);
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);
            adapter.Fill(dt);
            return dt;
        }

        public static City GetCityByID(int id)
        {
            DataTable dt=new DataTable();
            OleDbConnection connection = new OleDbConnection();
            OleDbConnection connect = BasePath.connect;
            OleDbCommand command = new OleDbCommand();
            command.Connection = connect;
            command.CommandText = "select * from city where id=@id";
            command.Parameters.AddWithValue("@id",id);
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);
            adapter.Fill(dt);
            DataTableReader dtr = dt.CreateDataReader();
            City c = new City();
            while (dtr.Read())
            {
                c.Id = Convert.ToInt32(dtr.GetValue(0));
                c.Name = dtr.GetValue(1).ToString();
                c.UserID = Convert.ToInt32(dtr.GetValue(2));
                c.Date = Convert.ToDateTime(dtr.GetValue(3));
            }
            return c;
        }

        public static void AddCity(City c)
        {
            OleDbConnection connection = new OleDbConnection();
            OleDbConnection connect = BasePath.connect;
            OleDbCommand command = new OleDbCommand();
            command.Connection = connect;
            command.CommandText = "insert into city (id,[name],[userid],[date]) values(@id,@name,@userid,@date)";
            command.Parameters.AddWithValue("@id", c.Id);
            command.Parameters.AddWithValue("@name", c.Name);
            command.Parameters.AddWithValue("@userid", c.UserID);
            command.Parameters.AddWithValue("@date", c.Date);
            
            command.ExecuteNonQuery();
            
        }

        public static void AddCity(City c, OleDbConnection connect)
        {
            OleDbCommand command = new OleDbCommand();
            command.Connection = connect;
            command.CommandText = "insert into city (id,[name],[userid],[date]) values(@id,@name,@userid,@date)";
            command.Parameters.AddWithValue("@id", c.Id);
            command.Parameters.AddWithValue("@name", c.Name);
            command.Parameters.AddWithValue("@userid", c.UserID);
            command.Parameters.AddWithValue("@date", c.Date);
            command.ExecuteNonQuery();
        }

        public static void UpdateCity(City c)
        {
            OleDbConnection connection = new OleDbConnection();
            OleDbConnection connect = BasePath.connect;
            OleDbCommand command = new OleDbCommand();
            command.Connection = connect;
            command.CommandText = "update city set [name]=@name,[userid]=@userid,[date]=@date where id=@id";
            command.Parameters.AddWithValue("@name", c.Name);
            command.Parameters.AddWithValue("@userid", c.UserID);
            command.Parameters.AddWithValue("@date", c.Date);
            command.Parameters.AddWithValue("@id", c.Id);
            
            command.ExecuteNonQuery();
            
        }

        public static void UpdateCity(City c, OleDbConnection connect)
        {
            OleDbCommand command = new OleDbCommand();
            command.Connection = connect;
            command.CommandText = "update city set [name]=@name,[userid]=@userid,[date]=@date where id=@id";
            command.Parameters.AddWithValue("@name", c.Name);
            command.Parameters.AddWithValue("@userid", c.UserID);
            command.Parameters.AddWithValue("@date", c.Date);
            command.Parameters.AddWithValue("@id", c.Id);
            command.ExecuteNonQuery();
        }

        public static void RemoveCityByID(int id)
        {
            OleDbConnection connection = new OleDbConnection();
            OleDbConnection connect = BasePath.connect;
            OleDbCommand command = new OleDbCommand();
            command.Connection = connect;
            command.CommandText = "delete from city where id=@id";            
            command.Parameters.AddWithValue("@id", id);
            
            command.ExecuteNonQuery();
            
        }
    }
}
